﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Common;
using SYH.Config;
using System.Data;
using System.Data.SqlClient;

namespace SYH.News.DAL
{
    public class PublicNews
    {
        #region 用户发布新闻数量
        /// <summary>
        /// 得到用户发布新闻数量
        /// </summary>
        /// <param name="userid">用户Id</param>
        /// <returns></returns>
        public int GetCountNews(int userid)
        {
            string sql = string.Format(" select count(1)  FROM [Public_News] where UserId= {0} and IsDelete=0 ", userid);
            return TypeParse.ObjectToInt(SqlHelper.ExecuteScalar(WebConfig.SqlConnString, CommandType.Text, sql), 0);
             
        }

        #endregion

        #region 新闻实体
        /// <summary>
        /// 新闻实体
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public SYH.News.Model.PublicNews GetModel(int Id)
        {
            SqlParameter[] parameter = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameter[0].Value = Id;

            SqlDataReader sdr = SqlHelper.ExecuteReader(WebConfig.SqlConnString, CommandType.StoredProcedure, "Public_News_GetModel", parameter);
            if (sdr.HasRows)
            {
                Model.PublicNews model = new Model.PublicNews();
                sdr.Read();
                model.Id = sdr.GetInt32(sdr.GetOrdinal("Id"));
                model.UserId = sdr.GetInt32(sdr.GetOrdinal("UserId"));

                model.Title = sdr.GetString(sdr.GetOrdinal("Title"));
                model.Keywords = sdr.GetString(sdr.GetOrdinal("Keywords"));
                model.Summary = sdr.GetString(sdr.GetOrdinal("Summary"));
                model.Contents = sdr.GetString(sdr.GetOrdinal("Contents"));
                model.ColumnId = sdr.GetInt32(sdr.GetOrdinal("ColumnId"));
                model.ViewCount = sdr.GetInt32(sdr.GetOrdinal("ViewCount"));
                model.IsDelete = sdr.GetInt32(sdr.GetOrdinal("IsDelete"));
                model.Status = sdr.GetInt32(sdr.GetOrdinal("Status"));
                model.CreateDate = sdr.GetDateTime(sdr.GetOrdinal("CreateDate"));

                sdr.Close();
                return model;
            }
            sdr.Close();
            return null;
        }
        #endregion

        #region 新闻添加
        /// <summary>
        /// 新闻添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns>受影响行数</returns>
        public int NewsAdd(SYH.News.Model.PublicNews model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.Int,4),
					new SqlParameter("@Title", SqlDbType.NVarChar,100),
					new SqlParameter("@Keywords", SqlDbType.NVarChar,100),
					new SqlParameter("@Summary", SqlDbType.NVarChar,255),
					new SqlParameter("@Contents", SqlDbType.NVarChar),
					new SqlParameter("@ColumnId", SqlDbType.Int,4),
					new SqlParameter("@ViewCount", SqlDbType.Int,4),
					new SqlParameter("@IsDelete", SqlDbType.Int,4),
					new SqlParameter("@Status", SqlDbType.Int,4),
                    new SqlParameter("@flag", SqlDbType.Int,4)};

            parameters[0].Value = model.UserId;
            parameters[1].Value = model.Title;
            parameters[2].Value = model.Keywords;
            parameters[3].Value = model.Summary;
            parameters[4].Value = model.Contents;
            parameters[5].Value = model.ColumnId;
            parameters[6].Value = model.ViewCount;
            parameters[7].Value = model.IsDelete;
            parameters[8].Value = model.Status;
            parameters[9].Value = model.Flag;

            int result = SqlHelper.ExecuteNonQuery(WebConfig.SqlConnString, CommandType.StoredProcedure, "Public_News_Add", parameters);
            return result;
        }
        #endregion

        #region 新闻修改
        /// <summary>
        /// 新闻修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int NewsUpdate(SYH.News.Model.PublicNews model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Public_News set ");
            strSql.Append("Title=@Title,");
            strSql.Append("Keywords=@Keywords,");
            strSql.Append("Summary=@Summary,");
            strSql.Append("Contents=@Contents,");
            strSql.Append("ColumnId=@ColumnId,");
            strSql.Append("Status=@Status,");
            strSql.Append("CreateDate=@CreateDate");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Title", SqlDbType.NVarChar,100),
					new SqlParameter("@Keywords", SqlDbType.NVarChar,100),
					new SqlParameter("@Summary", SqlDbType.NVarChar,255),
					new SqlParameter("@Contents", SqlDbType.NVarChar),
					new SqlParameter("@ColumnId", SqlDbType.Int,4),
                    new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@CreateDate", SqlDbType.DateTime),
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = model.Title;
            parameters[1].Value = model.Keywords;
            parameters[2].Value = model.Summary;
            parameters[3].Value = model.Contents;
            parameters[4].Value = model.ColumnId;
            parameters[5].Value = model.Status;
            parameters[6].Value = model.CreateDate;
            parameters[7].Value = model.Id;

            int rows = SqlHelper.ExecuteNonQuery(WebConfig.SqlConnString, CommandType.Text, strSql.ToString(), parameters);

            return rows;
        }
        #endregion

        #region 新闻删除
        /// <summary>
        /// 新闻删除
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="idList"></param>
        /// <returns></returns>
        public int NewsDelete(int userId, string idList)
        {
            string[] idArray = idList.Split(',');
            SqlParameter[] parameters = {
                    new SqlParameter("@IdList", SqlDbType.VarChar,200),
                    new SqlParameter("@Num", SqlDbType.Int,4)
                    };
            parameters[0].Value = idList;
            parameters[1].Value = idArray.Length;
            int rows = SqlHelper.ExecuteNonQuery(WebConfig.SqlConnString, CommandType.StoredProcedure, "Public_News_Delete", parameters);
            return rows;
        }
        #endregion

        #region 新闻设置置顶
        /// <summary>
        /// 新闻设置置顶
        /// </summary>
        /// <param name="userId">用户编号</param>
        /// <param name="newId">新闻编号</param>
        /// <param name="isTop">1：置顶  0：取消</param>
        /// <returns></returns>
        public int NewsSetTop(int userId, int newId, int isTop)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@UserId", SqlDbType.Int,4),
                    new SqlParameter("@NewId", SqlDbType.Int,4),
					new SqlParameter("@IsTop", SqlDbType.Int,4)
					};
            parameters[0].Value = userId;
            parameters[1].Value = newId;
            parameters[2].Value = isTop;

            int result = SqlHelper.ExecuteNonQuery(WebConfig.SqlConnString, CommandType.StoredProcedure, "Public_News_IsTop", parameters);
            return result;
        }
        #endregion

        #region  新闻分页数据
        /// <summary>
        /// 新闻分页数据
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="userId"></param>
        /// <param name="status">状态</param>
        /// <param name="pageCount"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public DataTable GetPageData(int pageSize, int pageIndex, int userId, int status, out int pageCount, out int recordCount)
        {
            string strCondition = string.Format(" and UserId={0} and Status={1} and IsDelete=0 ", userId, status);
            DataTable dt = Common.PublicSqlData.SimplePager(WebConfig.SqlConnString,
                                                            "Public_News",
                                                            "Id,UserId,Title,ColumnId,ViewCount,IsDelete,Status,IsTop,CreateDate,Summary",
                                                            pageSize,
                                                            pageIndex,
                                                            "CreateDate",
                                                            true,
                                                            strCondition,
                                                            out pageCount,
                                                            out recordCount).Tables[0];

            return dt;
        }
         #endregion

        #region 新闻状态数
        /// <summary>
        /// 新闻状态数
        /// </summary>
        /// <param name="status"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int StatusCount(int status, int userId)
        {
            string strsql = "select COUNT(1) from Public_News where UserId=@UserId and Status = @status and IsDelete = 0";
            SqlParameter[] parameters = {
                    new SqlParameter("@status",SqlDbType.Int,4),
                     new SqlParameter("@UserId",SqlDbType.Int,4)
                    };
            parameters[0].Value = status;
            parameters[1].Value = userId;
            object obj = Common.SqlHelper.ExecuteScalar(WebConfig.SqlConnString, CommandType.Text, strsql, parameters);

            return Common.TypeParse.ObjectToInt(obj, 0);
        }
        #endregion
    }
}
